package com.qf.dao.impl;

import com.qf.dao.ProductDao;
import com.qf.pojo.Product;
import com.qf.utils.DruidJdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.time.Duration;
import java.util.List;

/**
 * @author 高圆圆
 * @date 2023/6/2 11:21
 * 商品的数据访问接口实现
 */
public class PrductDaoImpl implements ProductDao {
    /**
     * 数据访问接口查询最新商品
     * @return 返回9条商品数据
     */
    @Override
    public List<Product> selectnewProducts() throws SQLException {
        QueryRunner qr  = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        //sql
        String sql = "SELECT * FROM product ORDER BY pdate DESC  LIMIT 9" ;
        //查询
        List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class));
        return list ;
    }

    /**
     * 数据访问接口查询热门商品
     * @return 返回9条商品数据
     */
    @Override
    public List<Product> selecthotProducts() throws SQLException {
        QueryRunner qr  = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        //sql
        String sql = "SELECT * FROM product WHERE is_hot = 1  LIMIT 9" ;
        //查询
        List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class));
        return list ;
    }

    /**
     * 根据商品id查询指定商品
     * @param pid 商品编号
     * @return 返回商品实体
     */
    @Override
    public Product selectProductByPid(String pid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        //sql
        String sql = "select * from product where pid = ?" ;
        //查询
        Product product = qr.query(sql, new BeanHandler<>(Product.class), pid);
        return product;
    }


    /**
     * 通过商品的分类id获取总记录数
     * @param cid 商品分类id
     * @return 返回总记录数
     */
    @Override
    public int getTotalCount(String cid) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        //sql
        String sql = "SELECT COUNT(pid) FROM product WHERE cid = ? " ;
        Object obj = qr.query(sql, new ScalarHandler<>(), cid);
        int totalCount = Integer.parseInt(String.valueOf(obj)) ;
        return totalCount;
    }

    /**
     * 数据访问接口完成商品分页查询
     * @param cid  分类id
     * @param start 起始行数
     * @param pageSize 每页显示的条数
     * @return 返回商品分页信息列表
     */
    @Override
    public List<Product> selectProductByPage(String cid, int start, int pageSize) throws SQLException {
        QueryRunner qr  = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        //sql
        String sql = "SELECT * FROM product WHERE cid = ? LIMIT ?,? " ;
        List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class), cid, start, pageSize);
        return list;
    }


    /**
     * 获取商品的总记录数
     * @return 返回总记录数
     */
    @Override
    public int selectTotalCount() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        String sql = "select count(pid) from product" ;
        Object obj = qr.query(sql, new ScalarHandler<>());
        int totalCount = Integer.parseInt(String.valueOf(obj));
        return totalCount;
    }

    /**
     * 管理员获取商品分页数据
     * @param currentPage 当前页码
     * @param pageSize 每页显示的条数
     * @return
     */
    @Override
    public List<Product> selectProducts(int currentPage, int pageSize) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
        String sql = "select * from product limit ?,?" ;
        List<Product> list = qr.query(sql, new BeanListHandler<>(Product.class), (currentPage - 1) * pageSize, pageSize);
        return list;
    }
}
